import yaml
from card_manager_system.card_class import Card
from utils.mysql_util import create_connection, close_connection, execute_dml, execute_query


def open_mysqldb_config():
    with open('..\\config\\config.yaml', 'r', encoding='utf-8') as file:
        config = yaml.safe_load(file)
        mysqldb_config = config['mysqldb']
        return mysqldb_config


def get_conn():
    config = open_mysqldb_config()
    host = config['host']
    port = config['port']
    user = config['user']
    password = config['password']
    database = config['database']
    return create_connection(host, port, user, password, database)


def close_conn(conn):
    close_connection(conn)


def create_card(card: Card):
    sql = """
    INSERT INTO user_card (name, phone, qq, email)
    VALUES (%s, %s, %s, %s)
    """
    params = (card.name, card.phone, card.qq, card.email)
    conn = get_conn()
    affect_num = execute_dml(conn, sql, params)
    close_conn(conn)
    return affect_num


def show_card(card: Card = None):
    sql = """
    SELECT * FROM user_card where 1 = 1
    """
    params = []
    if card:
        if card.name:
            sql += " and name = %s"
            params.append(card.name)
        if card.card_id:
            sql += " and card_id = %s"
            params.append(card.card_id)
    sql += " order by card_id"
    conn = get_conn()
    user_cards = execute_query(conn, sql, params)
    close_conn(conn)
    cards = []
    if not user_cards:
        return []
    for user_card in user_cards:
        card_id = user_card['card_id']
        name = user_card['name']
        phone = user_card['phone']
        qq = user_card['qq']
        email = user_card['email']
        cards.append(Card(name, phone, qq, email, card_id))
    return cards


def update_card(card: Card):
    if not card.name and not card.phone and not card.qq and not card.email:
        return 0
    sql = """
        update user_card set 
       """
    params = []
    if card.name:
        sql += " name = %s,"
        params.append(card.name)
    if card.phone:
        sql += " phone = %s,"
        params.append(card.phone)
    if card.qq:
        sql += " qq = %s,"
        params.append(card.qq)
    if card.email:
        sql += " email = %s,"
        params.append(card.email)
    sql = sql[:-1] + " where card_id = %s"
    params.append(card.card_id)
    conn = get_conn()
    effect_count = execute_dml(conn, sql, params)
    close_conn(conn)
    return effect_count


def delete_card(card_id):
    sql = """
    delete from user_card where card_id = %s
    """
    params = (card_id,)
    conn = get_conn()
    effect_count = execute_dml(conn, sql, params)
    close_conn(conn)
    return effect_count
